package my.spring.db;

import my.project.util.Log;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * MySQL jdbc 简单封装
 */
public final class MySQL {
    public static String driverClassName;
    public static String url;
    public static String username;
    public static String password;

    // 初始化，并测试数据库连接
    public static void test(String driverClassName, String url, String username, String password) {
        MySQL.driverClassName = driverClassName;
        MySQL.url = url;
        MySQL.username = username;
        MySQL.password = password;
        try {
            List<Map<String, Object>> data = executeQuery("SELECT VERSION() AS ver", null);
            if (!data.isEmpty()) {
                String ver = (String) data.get(0).get("ver");
                Log.log("数据库连接成功，版本为：{}", ver);
            }
        } catch (DbException e) {
            Log.log("连接数据库失败！");
            e.printStackTrace();
        }
    }

    // 连接数据库
    public static Connection connect() throws DbException {
        try {
            Class.forName(driverClassName);
            return DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            throw new DbException("连接数据失败，" + e.getMessage());
        }
    }

    // 执行查询SQL
    public static List<Map<String, Object>> executeQuery(String sql, Object[] args) throws DbException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Map<String, Object>> data = new ArrayList<>();
        try {
            conn = connect();
            stmt = conn.prepareStatement(sql);
            addParams(sql, stmt, args);
            rs = stmt.executeQuery();
            ResultSetMetaData rsData = rs.getMetaData();
            int columnCount = rsData.getColumnCount();
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    String key = rsData.getColumnName(i + 1); // 列名 TODO 空值或重复问题
                    Object val = rs.getObject(i + 1);
                    row.put(key, val);
                }
                data.add(row);
            }
            return data;
        } catch (Exception e) {
            e.printStackTrace();
            throw new DbException("执行查询SQL失败，" + e.getMessage());
        } finally {
            close(conn, stmt, rs);
        }
    }

    // 执行更新SQL
    public static Integer executeUpdate(String sql, Object[] args) throws DbException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = connect();
            stmt = conn.prepareStatement(sql);
            addParams(sql, stmt, args);
            return stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new DbException("执行更新SQL失败，" + e.getMessage());
        } finally {
            close(conn, stmt, null);
        }
    }

    // STMT添加参数
    private static void addParams(String sql, PreparedStatement stmt, Object[] args) throws SQLException {
        Log.log("原始SQL: {}", sql);
        Log.log("SQL参数: {}", args == null ? "无" : args);
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                Object o = args[i];
                if (o instanceof String) {
                    stmt.setString(i + 1, (String) o);
                } else if (o instanceof Integer) {
                    stmt.setInt(i + 1, (Integer) o);
                } else {
                    // TODO 其他类型暂不考虑
                }
            }
        }
    }

    // 安全关闭连接
    public static void close(Connection conn, PreparedStatement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
